import json
import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows


def reconstruct_table(json_path, output_excel):
    # 读取JSON数据
    with open(json_path, 'r', encoding='utf-8') as f:
        data = json.load(f)

    # 提取每个单元格的坐标和文本
    cells = []
    for item in data:
        if not item['text'].strip():
            continue
        x_coords = item['position'][::2]
        y_coords = item['position'][1::2]
        x_min, x_max = min(x_coords), max(x_coords)
        y_min, y_max = min(y_coords), max(y_coords)
        cells.append({
            'x_min': x_min,
            'y_min': y_min,
            'text': item['text'].strip()
        })

    # 确定行和列的分界
    y_coords = sorted(list({c['y_min'] for c in cells}))
    x_coords = sorted(list({c['x_min'] for c in cells}))

    # 创建坐标到行列的映射（带容差）
    y_tol = (max(y_coords) - min(y_coords)) / 100
    x_tol = (max(x_coords) - min(x_coords)) / 100

    row_indices = {}
    for i, y in enumerate(y_coords):
        for cell in cells:
            if abs(cell['y_min'] - y) <= y_tol:
                row_indices[cell['y_min']] = i

    col_indices = {}
    for i, x in enumerate(x_coords):
        for cell in cells:
            if abs(cell['x_min'] - x) <= x_tol:
                col_indices[cell['x_min']] = i

    # 构建表格矩阵
    max_row = max(row_indices.values()) + 1
    max_col = max(col_indices.values()) + 1
    table = [['' for _ in range(max_col)] for _ in range(max_row)]

    for cell in cells:
        row = row_indices[cell['y_min']]
        col = col_indices[cell['x_min']]
        table[row][col] = cell['text']

    # 转换为DataFrame并保存Excel
    df = pd.DataFrame(table)
    df = df.replace('', None)

    wb = Workbook()
    ws = wb.active

    for r in dataframe_to_rows(df, index=False, header=False):
        ws.append(r)

    # 调整列宽
    for col in ws.columns:
        max_length = 0
        column = col[0].column_letter
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2)
        ws.column_dimensions[column].width = adjusted_width

    wb.save(output_excel)


# 使用示例
reconstruct_table('C:/Users/zhuka/Downloads/00_rec.json', 'C:/Users/zhuka/Downloads/00_rec_05.xlsx')
